﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace LFramework.Data
{
    /// <summary>
    /// SQL解析
    /// </summary>
    public static class SQLResolver
    {
        /// <summary>
        /// 解析变量
        /// </summary>
        public static string Resolve_Variable(string sql, string nodeCode, string beginDate, string endDate)
        {
            //解析FUNNODE
            sql = sql.Replace("[FUNNODE]", @"""" + nodeCode + @"""");

            //解析开始日期，结束日期
            sql = sql.Replace("[BeginDate]", beginDate);
            sql = sql.Replace("[EndDate]", endDate);

            //解析GetUnionSql
            sql = ExplainUnionSql(sql);

            return sql;
        }

        /// <summary>
        /// 解析表名
        /// </summary> 
        public static string Resolve_TableName(string sql, string defTableName, string actTableName)
        {
            sql = sql.TrimStart('#');
            return sql.Replace(defTableName, actTableName);
        }

        /// <summary>
        /// 解释输入文本中的UnionAllSql，并返回解释后的文本
        /// 格式: GetUnionSQL [ SqlStr | StartYM | EndYM ]
        /// 解释: 将SqlStr 中的000000字符串，替换成相应年月，并UNION ALL
        /// 例: GetUnionSql [ Select * From TB000000_Stock | 200701 | 200703 ]
        /// 得到的结果为
        /// Select * From TB200701_Stock Union All
        /// Select * From TB200702_Stock Union All
        /// Select * From TB200703_Stock
        /// </summary>
        /// <param name="inText">输入字符串</param>
        private static string ExplainUnionSql(string inText)
        {
            try
            {
                string finalText = inText;
                int indexGetUnionSql = finalText.IndexOf("GetUnionSQL");
                string strEndYM = "";
                string strStartYM = "";
                string strSql = "";
                while (indexGetUnionSql != -1)
                {
                    int indexFirstBracket = finalText.IndexOf("[", indexGetUnionSql);
                    int indexLastBracket = finalText.IndexOf("]", indexGetUnionSql);
                    string oldvalue = finalText.Substring(indexGetUnionSql, indexLastBracket - indexGetUnionSql + 1);
                    int indexOldValueBracket = oldvalue.IndexOf("[");
                    int indexLastUp = oldvalue.LastIndexOf("|");
                    int indexSecondLastUp = oldvalue.Substring(0, indexLastUp - 1).LastIndexOf("|");
                    strEndYM = oldvalue.Substring(indexLastUp + 1, oldvalue.Length - indexLastUp - 2).Trim();
                    strStartYM = oldvalue.Substring(indexSecondLastUp + 1, indexLastUp - indexSecondLastUp - 1).Trim();
                    strSql = finalText.Substring(indexFirstBracket + 1, indexSecondLastUp - indexOldValueBracket - 1).Trim();
                    DateTime EndDate = DateTime.ParseExact(strEndYM + "01", "yyyyMMdd", null);
                    DateTime StartDate = DateTime.ParseExact(strStartYM + "01", "yyyyMMdd", null);
                    int distance = 0;//月份间隔
                    if (EndDate.Year == StartDate.Year)
                    {
                        distance = (EndDate.Month - StartDate.Month) + 1;
                    }
                    else
                    {
                        distance = (EndDate.Year - StartDate.Year) * 12 + EndDate.Month - StartDate.Month + 1;
                    }
                    string newSql;
                    string replaceSql = "";//用于替换的sql
                    for (int i = 0; i < distance; i++)
                    {
                        newSql = strSql.Replace("000000", StartDate.AddMonths(i).ToString("yyyyMM"));

                        if (i != distance - 1)
                        {
                            replaceSql += newSql + " Union All ";
                        }
                        else
                        {
                            replaceSql += newSql;
                        }
                    }
                    finalText = finalText.Replace(oldvalue, replaceSql);
                    indexGetUnionSql = finalText.IndexOf("GetUnionSQL");
                }
                return finalText;
            }
            catch (System.ApplicationException ex)
            {
                throw ex;
            }

        }

    }
}
